package com.impinj.program;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import com.impinj.octane.TagReadOpResult;

public class JDBCUtils {
	
	private static String driver;
	private static String url;
	private static String username;
	private static String password;
	
	
	/* 目录
	 * 首字母升序排序
	 * static
	 * assetsInventory					新建数据库			64
	 * closeConnection					释放数据库连接		93
	 * creatStatement					创建操作环境		120
	 * detectWhetherTagIsInTheRoom		检测标签是否在房间	141
	 * getConnection					连接数据库			165
	 * isTableExist						检测表格是否存在		189
	 * overwritingRoom					覆写房间			210
	 * removeDuplicatesInSql			表格去重			246
	 * writeResult						结果写入			279
	 */
	
	// 静态语句
	static {
		// 用这个类做对象，拿到这个包（class）,用类记载器（getclassloader）,以流的形式获取资源（getresourceasstream）
		InputStream inputRecourse = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
		
		// 创建 properties 类型的对象
		// 第一次写的时候下面有红色波浪线，需要导包（import java.util.Properties）
		Properties p = new Properties();
		
		try {
			// 加载流文件
			p.load(inputRecourse);
			driver = p.getProperty("driver");
			url = p.getProperty("url");
			username = p.getProperty("username");
			password = p.getProperty("password");
			// 加载驱动
			Class.forName(driver);
			System.out.println("》》》properties配置文件加载成功《《《");
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}}
	
	/**  
	 * @Title: assetsInventory
	 * @Description: 用于新建关于"资产盘点"的数据库
	 * @return 一个数据库的 connection
	 * @author Black00white
	 * @date 2024-01-07 22:03:34 
	 */  
	public static void bulidAssetsInventory() {
		// 尝试新建 assetsInventory 数据库
		// try 中的地址后面跟着是数据库的用户名以及密码
		try (Connection connection = DriverManager.getConnection(url, username, password);
				Statement statement = connection.createStatement()) {
			// 查找是否存在 assetsinventory 这个数据库
			ResultSet resultSet = statement.executeQuery("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'assetsinventory'");
			// 有返回结果不新建，无返回结果新建数据库
            if (resultSet.next()) {
                System.out.println("数据库 'assetsinventory' 已经存在，不需要新建。");
            } else {
                // 检测到没有 assetsinventory 数据库，创建新数据库
                statement.executeUpdate("CREATE DATABASE assetsinventory");
                System.out.println("数据库 'assetsinventory' 创建成功。");
            }
            // 关闭数据库相关资源
		    statement.close();
		    connection.close();
			} catch (SQLException e) {
				System.out.println("错误原因 : " + e);
			}
		
	}
	
	/**  
	 * @Title: closeConnection
	 * @Description: 释放数据库连接
	 * @author Black00white
	 * @date 2024-01-07 10:18:12 
	 */  
	public static void closeConnection() {
		try {
			// 释放数据库的连接
			JDBCUtils.getConnection().close();
			System.out.println("数据库连接释放成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("数据库连接释放失败，因为 : " + e);
		}
		
	}
	
	/**  
	 * @Title: createStatement
	 * @Description: 创建数据库的环境，在这里会检查是否有数据库与表格
	 * @return 返回一个数据库连接
	 * @throws SQLException
	 * @author Black00white
	 * @date 2024-01-07 10:17:29 
	 */  
	public static Statement createStatement() throws SQLException {
		// 连接数据库
		Connection connection = JDBCUtils.getConnection();
		// 返回 creatStatement 的值
		if (connection != null) {
			return connection.createStatement();
		} else {
			System.out.println("执行环境创建失败，无法获取数据库连接");
			return null;
		}
	}
	
	/**  
	 * @Title: detectWhetherTagIsInTheRoom
	 * @Description: 对比数据库，显示标签是否在房间中
	 * @throws SQLException
	 * @author Black00white
	 * @date 2024-01-07 10:15:19 
	 */  
	public static void detectWhetherTagIsInTheRoom(Statement statement, String roomName) {
		// 查询标签在两个库之间的依赖关系，显示不在库的标签
		ResultSet result2;
		try {
			result2 = statement.executeQuery("SELECT userBlock "
								        		+ "FROM " + roomName + "_r "
								        		+ "WHERE userBlock NOT IN ( "
								            		+ "SELECT userBlock "
								            		+ "FROM " + roomName + " )");
			// 显示部分
			while (result2.next()) {
				System.out.print("物品： ");
				System.out.print(result2.getString("userBlock"));
				System.out.println(" 已不在房间 " + roomName + " 中");
			} 
		}catch (SQLException e) {
			System.out.println("未检测到数据库'roomName_r'，请先进行覆写");
			e.printStackTrace();
		}
	}
	
	/**   
	 * @Title: getConnection
	 * @Description: 获得连接对象的方法
	 * @return 返回一个 connection 
	 * @author Black00white
	 * @date 2024-01-07 10:18:24 
	 */  
	public static Connection getConnection() {
		try {
			return DriverManager.getConnection(url, username, password);
			// 因为 DriverManager 是 Connection 类型的对象，所以最后直接返回就行
		} catch (SQLException e) {
			System.out.println("数据库连接失败");
			e.printStackTrace();
		}
		return null;
	}
	
	/**  
	 * @Title: isTableExist
	 * @Description: 检查是否存在房间表格，不存在新建，存在无反馈
	 * @param statement
	 * @param tempTable
	 * @author Black00white
	 * @date 2024-01-13 01:31:40 
	 * @why 考虑到后期sequence序列可能被写满报错，每次读仓库的表格的时候把原先的表格丢弃，新建表格读取
	 */  
	public static void isTableExist(Statement statement, String tempTable) {
		try {
			// 丢弃原先的表格
			String dropTableString = "DROP TABLE IF EXISTS " + tempTable;
			statement.executeUpdate(dropTableString);
			
			// 新建表格
			String sqlTable = "CREATE TABLE IF NOT EXISTS " + tempTable + "("
					+ "sequence BIGINT AUTO_INCREMENT PRIMARY KEY,"
					+ "userBlock VARCHAR(255)"
					+ ", roomname VARCHAR(255)"
					+ ")";
			statement.execute(sqlTable);
			System.out.println("create table "+tempTable);
		} catch (SQLException e) {
			System.out.println("发生未知错误，因为 : " + e);
		}
	}
	
	/**  
	 * @Title: overwritingRoom
	 * @Description: 用于覆写房间数据库
	 * @throws SQLException
	 * @author Black00white
	 * @date 2024-01-08 10:46:30 
	 */  
	public static void overwritingRoom(Statement statement, String scRoomNum) throws SQLException{
		System.out.println("即将覆写房间 " + scRoomNum);
		
		// 丢弃原先的表格
		String dropTableString = "DROP TABLE IF EXISTS " + scRoomNum + "_r";
		statement.executeUpdate(dropTableString);
		
 		// 新建房间总表格
 		String createTableSQL_r = "CREATE TABLE IF NOT EXISTS " + scRoomNum + "_r ("
 				+ "sequence BIGINT AUTO_INCREMENT PRIMARY KEY,"
 				+ "userBlock VARCHAR(255)"
 				+ ")";
 		statement.executeUpdate(createTableSQL_r);
 		System.out.print("表 " + scRoomNum + "_r 创建成功； ");
 		
 		String createTableSQL = "CREATE TABLE IF NOT EXISTS " + scRoomNum + "("
 				+ "sequence BIGINT AUTO_INCREMENT PRIMARY KEY,"
 				+ "userBlock VARCHAR(255)"
 				+ ")";
 		statement.executeUpdate(createTableSQL);
 		System.out.println("表 " + scRoomNum + " 创建成功");
	}
	
 	/**  
	 * @Title: removeDuplicatesInSql
	 * @Description: 数据库去重算法
	 * @throws SQLException
	 * @author Black00white
	 * @date 2024-01-07 10:15:46 
	 */  
	public static void removeDuplicatesInSql(Statement statement, String roomName) {
		// 去重，保留 sequence 最小的 userBlock
		try {
			statement.executeUpdate("DELETE FROM  " + roomName
			        + " WHERE sequence NOT IN ( "
			            + "SELECT dt.sequence "
			            + "FROM ( "
			                + "SELECT MIN(sequence) AS sequence "
			                + "FROM " + roomName
			                + " GROUP BY userBlock "
			            + ") dt "
			        + ")");
			System.out.print("表格'" + roomName + "'去重结束；	");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("未找到去重的表格，请使用 overwrite 覆写新表格");
		}
	}
	
	/**  
	 * @Title: writeResult
	 * @Description: 数据库写入
	 * @author Black00white
	 * @date 2024-01-08 03:05:11 
	 */  
	public static void writeResult(Statement statement, TagReadOpResult tr, String tableName) {
		try {			
			String allMessageString = tr.getData().toHexWordString();
			String roomnameString = allMessageString.substring(0, 4);
			String userBlockMessageString = allMessageString.substring(5, 29);
			System.out.println("准备写入："+tableName+"(roomname)"+roomnameString+" (userBlock)"+userBlockMessageString);
        	// 构建 SQL 语句，不提供具体的 sequence 值
        	String sql = "INSERT INTO " + tableName 
        					+ " (userBlock, roomname) VALUES ('" 
        					+ userBlockMessageString + "', '" + roomnameString + 
        					"')";
            // 执行 SQL 语句
            statement.executeUpdate(sql);
            System.out.println("数据写入成功");
		} catch (SQLException e) {
			System.out.println("数据写入异常，因为 : " + e);
		}
	}
	
	/**  
	 * @Title: getSameValueBetweenTwoTables
	 * @Description: 获取两个 roomName 表格的差异数
	 * @param statement[0]
	 * @param roomName
	 * @return
	 * @author Black00white
	 * @date 2024-03-09 04:50:34 
	 */  
	public static int getSameValueBetweenTwoTables(Statement statement, String roomName) {
	    int sameValuesBetweenTwoTables = 0;  
	    String sql = "SELECT COUNT(DISTINCT t1.userBlock)"  
	                  + " FROM " + roomName + "_r t1"  
	                  + " LEFT JOIN " + roomName + " t2"  
	                  + " ON t1.userBlock = t2.userBlock"  
	                  + " WHERE t2.userBlock IS NULL;";  
        try (ResultSet resultSet = statement.executeQuery(sql)) {  
            if (resultSet.next()) {  
                sameValuesBetweenTwoTables = resultSet.getInt(1);  
            }  
	    } catch (SQLException e) {  
	        e.printStackTrace();  
	    }  
	    return sameValuesBetweenTwoTables;  
	}
	
	/**  
	 * @Title: isTagInTable
	 * @Description: 检测单个标签是否在表格内
	 * @param statement
	 * @param tagMessage
	 * @param tableName
	 * @return
	 * @author Black00white
	 * @date 2024-06-02 09:58:25 
	 */  
	public static boolean isTagInTable(Statement statement, TagReadOpResult tr, String tableName) { 
		// 构建检查 sql 的语句
		String sql = "SELECT EXISTS (SELECT 1 FROM " 
						+ tableName + " WHERE userBlock = '" 
						+ tr.getData().toHexWordString().substring(5, 29) + "')";
		try {
			// 尝试进行重复度的查询
			ResultSet executeQuery = statement.executeQuery(sql);
			executeQuery.next();
			if (executeQuery.getInt(1) == 1) {
				System.out.println("存在重复的数据内容，不写入数据库");
				return true;
			}
			else {
				System.out.println("》》》新数据写入 " + tableName + "《《《");
				JDBCUtils.writeResult(statement, tr, tableName);
				return false;
			}
		} catch (SQLException e) {
			System.out.println("标签是否重复的检查发生错误， 错误原因是 ：" + e.getMessage());
			return false;
		}
	}
	
	/**  
	 * @Title: dropTable
	 * @Description: 删除表格
	 * @param statement
	 * @param tableName
	 * @author Black00white
	 * @date 2024-06-02 10:33:27 
	 */  
	public static void dropTableIfExists(Statement statement, String tableName) {
		// 构建 sql 语句
		String sql = "DROP TABLE IF EXISTS " + tableName;
		try {
			// 尝试丢弃表格
			statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

	public static void deleteData(Statement statement, String tableName, String condname, String condval){
		String sql = "delete from "+tableName+" where "+condname+"='"+condval+"';";
		System.out.println("从表格 " + tableName+ " 删除 "+condname+" 为 "+condval+" 的数据");
		try {
			// 尝试丢弃表格
			statement.execute(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
